Amazon QuickSightの分析で”ビジュアル”を利用した時のクエリを確認する方法

Athenaではクエリ成功するのにQuickSightの分析でうまくいかない時に

#Amazon Athena

#Amazon QuickSight

洲崎義人

2022.11.15

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon QuickSightは、情報を可視化することができるBIサービスです。
Amazon S3やAthena等、分析したいデータやテーブルをデータセットとして作成し、データセットから分析を行います。
分析はビジュアルと呼ばれる、データセットの内容に対してグラフィカルに表示する方法があります。
今回はビジュアルを利用した時に裏でクエリが走っていることに対してハマった為、その時のクエリの確認方法について記載します。
※本記事は下記のAthenaをQuickSightで表示させようと検証した時の内容です。SQLやテーブルは下記記事がベースとなっています。

ビジュアル

QuickSightのビジュアルは、データセットのグラフィカルな表現方法になります。
ビジュアルタイプとして複数用意されており、データセットの内容に対して円グラフや棒グラフ、テーブルといった形で表示することができます。
(下記アイコンをクリックする形です)

全部で30パターン用意されてます。詳細について下記ドキュメントをご確認ください。

何が起こったか

ビジュアルを利用すると、選択したビジュアルタイプに応じて、裏側でクエリが走り、その結果をグラフィカルに表示します。
この時、データセットに設定しているカスタムSQL等がある場合、ビジュアルタイプのクエリと一緒に実行されます。
Amazon Athenaで上手くいったクエリを、そのままAmazon QuickSightのカスタムSQLで設定しても、ビジュアルタイプによって上手く表示されない可能性があります。
カスタムSQLと、ビジュアルタイプのクエリが上手くハマらない場合、下記のようなSQL例外エラーが発生します。

”詳細を表示”をクリックしても、リージョン・タイムスタンプ・requestIdしか表示されません。。

「Amazon Athenaでは上手くクエリが成功したのに、QuickSightにしたらエラーになるなんて、どんな処理が走ってるんだ。。」と1週間ぐらい頭を抱えてました。

QuickSightで実行されるクエリ内容の確認

QuickSightのビジュアルタイプを利用した裏側のクエリは、QuickSight上から確認することはできません。
どこで確認するかというと、Amazon Athenaの画面から確認します。
Amazon Athenaのコンソール画面を開き、最近のクエリをクリックします。

すると、QuickSightの"ビジュアル"を操作したクエリの状況を確認することができます。
(クエリに/* QuickSightと記載されているものが当てはまります)

エラーが起きた時のクエリをみると、設定したカスタムSQLの他にQuickSightで自動的に入力したSQLが追記されています。

SQL例外エラーの内容も見ることができます。

SYNTAX_ERROR: line 2:1: Type json is not orderable, and therefore cannot be used in ORDER BY: "RecordingLocation"

これで、トラブルシューティングが可能になります！(良かった)

比較

参考までに、カスタムSQLで設定したクエリと、ビジュアルタイプ”テーブル”を利用した時のクエリを比較します。
(SQLの細かい内容はこちらをご確認ください)
カスタムSQLで設定したクエリ

SELECT 
    ConnectedToSystemTimestamp,
    DisconnectTimestamp,
    Channel,
    ContactId,
    json_extract(Queue, '$.name') AS Queue,
    json_extract(Agent, '$.username') AS AgentName,
    json_extract(Recording, '$.location') AS RecordingLocation,
    json_extract(CustomerEndpoint, '$.address') AS CustomerEndpoint,
    json_extract(SystemEndpoint, '$.address') AS SystemEndpoint
FROM "default"."ctr_table"
WHERE year = 2022 and month=10 and day=5
limit 10

QuickSightのビジュアルタイプ”テーブル”で実行されたクエリ
※QuickSightで追記されたところをハイライトで表示しています。

/* QuickSight xxxxx-xxxx-xxxx-xxxx-xxxxxxxxx */
SELECT "RecordingLocation" AS "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx.RecordingLocation", COUNT(*) AS "count"
FROM (SELECT 
    ConnectedToSystemTimestamp,
    DisconnectTimestamp,
    Channel,
    ContactId,
    json_extract(Queue, '$.name') AS Queue,
    json_extract(Agent, '$.username') AS AgentName,
    json_extract(Recording, '$.location') AS RecordingLocation,
    json_extract(CustomerEndpoint, '$.address') AS CustomerEndpoint,
    json_extract(SystemEndpoint, '$.address') AS SystemEndpoint
FROM "default"."ctr_table"
WHERE year = 2022 and month=10 and day=5
limit 10) AS "新しいカスタム SQL"
GROUP BY "RecordingLocation"
ORDER BY "RecordingLocation" NULLS FIRST
LIMIT 500

カスタムSQLで設定した内容に対して追記してクエリをかけていることがわかります。
気になるところとして、16-17行目のGROUP BYとORDER BYが追記されています。
先ほどのAthenaのエラーが下記でした。

SYNTAX_ERROR: line 2:1: Type json is not orderable, and therefore cannot be used in ORDER BY: "RecordingLocation"

json_extractで出力している内容と、ORDER BYの記述は一緒に使えないという内容です。
つまり、QuickSightのビジュアルタイプ”テーブル”を利用する際は、自動でORDER BYが入る為、カスタムSQLのjson_extractの記載を変更するのが必要だと認識することができました。
(json_extractとORDER BYを両立する対応について、別途ブログ書きます)